package com.aaa.qy127.dao.impl;

import com.aaa.qy127.dao.ChuKuCheckDao;
import com.aaa.qy127.entity.ChuKuCheck;
import com.aaa.qy127.util.JDBCUtil;
import org.springframework.stereotype.Repository;

import java.util.*;

@Repository("chuKuCheckDaoImpl")
public class ChuKuCheckDaoImpl implements ChuKuCheckDao {
    /**
     * 查询 所有的出库 记录表中的信息 并 分页  模糊查询
     * @param chuKuCheck
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllChuKuCheck(ChuKuCheck chuKuCheck) {
        //select tc.ckid,tc.cdate,tc.uid,tc.totalprice,tc.totalcount,tc.jsr from t_ck tc INNER JOIN t_staff ts ON tc.uid = ts.id where cdate >= "2020-02-13"  and cdate <= "2020-08-01" and ts.staffName LIKE '%小%' and tc.jsr like '%李%' LIMIT 0,5  ;
        StringBuffer sql = new StringBuffer("select tc.ckjlid,tc.cdate,tc.uid,tc.totalprice,tc.totalcount,tc.jsr,ts.staffName,tc.status,tc.remark from t_ck_jl tc INNER JOIN t_staff ts ON tc.uid = ts.id where tc.status = 0");
        //ts.staffName LIKE '%小%' and tc.jsr like '%李%'
        if (chuKuCheck.getPerson() != null && chuKuCheck.getPerson() != ""){
            sql.append(" and ts.staffName like '%").append(chuKuCheck.getPerson() + "%'");
        }
        if (chuKuCheck.getJsr() != null && chuKuCheck.getJsr() != ""){
            sql.append(" and tc.jsr like '%").append(chuKuCheck.getJsr()+"%'");
        }
        //cdate >= "2020-02-13"  and cdate <= "2020-08-01"
        if (chuKuCheck.getBeginDate() != null && chuKuCheck.getBeginDate() != ""){
            sql.append(" and cdate >= '"+chuKuCheck.getBeginDate()+"'");
        }
        if (chuKuCheck.getOverDate() !=null && chuKuCheck.getOverDate() != ""){
            sql.append(" and cdate <= '"+chuKuCheck.getOverDate()+"'");
        }
        sql.append(" limit ?,?");
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql.toString(),(chuKuCheck.getPage()-1)*chuKuCheck.getLimit(),chuKuCheck.getLimit());
        //System.out.println(sql);
        return maps;
    }

    /**
     * 查询 所有的记录表中的信息的数量
     * @param chuKuCheck
     * @return
     */
    @Override
    public int getAllCount(ChuKuCheck chuKuCheck) {
        StringBuffer sql = new StringBuffer("select count(*) from t_ck_jl tc INNER JOIN t_staff ts ON tc.uid = ts.id  where tc.status != 0");
        if (chuKuCheck.getPerson() != null && chuKuCheck.getPerson() != ""){
            sql.append(" and ts.staffName like '%").append(chuKuCheck.getPerson() + "%'");
        }
        if (chuKuCheck.getJsr() != null && chuKuCheck.getJsr() != ""){
            sql.append(" and tc.jsr like '%").append(chuKuCheck.getJsr()+"%'");
        }
        //cdate >= "2020-02-13"  and cdate <= "2020-08-01"
        if (chuKuCheck.getBeginDate() != null && chuKuCheck.getBeginDate() != ""){
            sql.append(" and cdate >= '"+chuKuCheck.getBeginDate()+"'");
        }
        if (chuKuCheck.getOverDate() !=null && chuKuCheck.getOverDate() != ""){
            sql.append(" and cdate <= '"+chuKuCheck.getOverDate()+"'");
        }
        List<List> lists = JDBCUtil.queryList(sql.toString());
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;
    }

    /**
     * 查询所有的 出库记录 详情 表中的 未 审核 或者 审核没有成功 的 信息 并分页
     * @param chuKuCheck
     * @return
     */
    @Override
    public List<Map<String, Object>> getChuKuCheck(ChuKuCheck chuKuCheck) {
        String sql = "select td.cdjlid,td.bookid,tb.bookname,tb.author,tb.publisher,td.count,tc.jsr,tc.cdate,td.outprice,ts.staffName,tt.typename,tc.uid from t_book tb INNER JOIN t_ckjldetail td ON td.bookid = tb.bookid INNER JOIN t_ck_jl  tc ON tc.ckjlid = td.ckjlid INNER JOIN t_staff ts ON ts.id = tc.uid INNER JOIN t_type tt ON tb.typeid = tt.typeid where tc.ckjlid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, chuKuCheck.getCkjlid());
        //System.out.println(maps);
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 查询 按照 id 查询 该 出库记录的 数量
     * @param chuKuCheck
     */
    @Override
    public int getCount(ChuKuCheck chuKuCheck) {
        String sql = "select COUNT(*) from t_book tb INNER JOIN t_ckjldetail td ON td.bookid = tb.bookid INNER JOIN t_ck_jl  tc ON tc.ckjlid = td.ckjlid INNER JOIN t_staff ts ON ts.id = tc.uid INNER JOIN t_type tt ON tb.typeid = tt.typeid where tc.ckjlid = ?";
        List<List> lists = JDBCUtil.queryList(sql, chuKuCheck.getCkjlid());
        if (lists.size() >0){
            Object o = lists.get(0).get(0);
            int i = Integer.parseInt(o + "");
            return i;
        }
        return 0;
    }

    /**
     * 通过 ckjlid 修改 status 的值
     * @param ckjlid
     * @param status
     * @return
     */
    @Override
    public int updateStatus(String ckjlid, String status) {
        String sql = "update t_ck_jl set `status` = 1 where ckjlid = ?";
        int i = JDBCUtil.update(sql, ckjlid);
        return i;
    }

    /**
     * 通过 ckjlid 查询出 这一条 出库记录信息
     * @param ckjlid
     * @return
     */
    @Override
    public List<Map<String,Object>> getOneChuKuJLByckjlid(String ckjlid) {
        String sql = "select ckjlid,cdate,uid,totalprice,totalcount,jsr,status from t_ck_jl where ckjlid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, ckjlid);
        if (maps.size()>0){
            return maps;
        }
        return null;
    }

    /**
     * 通过 ckjlid 修改 记录表中的 时间
     * @param ckjlid
     * @return
     */
    @Override
    public int updateCdate(String ckjlid) {
        String sql = "update t_ck_jl set cdate = ? where ckjlid = ?";
        int i = JDBCUtil.update(sql, new Date(), ckjlid);
        return i;
    }

    /**
     * 通过 ckjlid 查询出 该 ckjlid 对应 详情记录
     * @param ckjlid
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllChuKuXQByckjlid(String ckjlid) {
        String sql = "select cdjlid,bookid,count,ckjlid,outprice from t_ckjldetail where ckjlid = ?";
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql, ckjlid);
        if (maps.size() >0){
            return maps;
        }
        return null;
    }

    /**
     * 将 从 出库记录 详情表中查出的数据 插入到 出库详情表中
     * @param maps
     * @return
     */
    @Override
    public void insertAllChuKuXQ(List<Map<String, Object>> maps,int ckid) {
        for (Map<String, Object> map : maps) {
            String sql ="insert into t_ckdetail VALUES(null,?,?,?,?)";
            JDBCUtil.update(sql,map.get("bookid"),map.get("count"),ckid,map.get("outprice"));
        }
    }

    /**
     * 将 从 出库记录表中 查出的 数据 插入到 出库 表中 并返回 主键
     * @param maps
     * @return
     */
    @Override
    public int insertOneChuKu(List<Map<String, Object>> maps) {
        int i = 0;
        for (Map<String, Object> map : maps) {
            String sql = "insert into t_ck VALUES(null,?,?,?,?,?,?)";
            i = JDBCUtil.getPrimaryKey(sql, map.get("cdate"), map.get("uid"), map.get("totalprice"), map.get("totalcount"), map.get("jsr"), map.get("status"));
        }
        return i;
    }

    /**
     * 通过 出库记录详情表中的bookid 查询出 每本书的 id 和 数量
     * @param maps
     * @return
     */
    @Override
    public List<List> getAllCountByBookid(List<Map<String, Object>> maps) {
        ArrayList<List> bigmaps = new ArrayList<>();
        for (Map<String, Object> map : maps) {
            String sql = "select tc.bookid,tc.count countC,tb.count countB from t_ckjldetail tc INNER JOIN t_book tb ON tc.bookid = tb.bookid where tc.bookid = ?";
            List<List> lists = JDBCUtil.queryList(sql, map.get("bookid"));
            //Map<String, Object> map1 = maps1.get(0);
            List list = lists.get(0);
            //System.out.println(list);
            bigmaps.add(list);
        }
        System.out.println(bigmaps);
        return bigmaps;
    }

    /**
     * 通过 查询到的 bookid 和 该书的 总量 和 该书 出库 的 数量 修改 book表中的书籍的数量
     * @param lists
     */
    @Override
    public void updateCountByBookidAndCount(List<List> lists) {
        for (List list : lists) {
            String sql = "update t_book set count = ? where bookid = ?";
            int countC = (int)list.get(1);
            //System.out.println(countC);
            int countB = (int)list.get(2);
            //System.out.println(countB);
            int i = JDBCUtil.update(sql, (countB - countC), list.get(0));
        }
    }

    /**
     * 通过 ckjlid 修改 状态 和 时间 和 备注的值
     * @param ckjlid
     * @return
     */
    @Override
    public int updateStatusByckjlid(String bz, String ckjlid) {
        String sql = "update t_ck_jl set cdate = ?,`status` = 2,remark = ? where ckjlid=?";
        int i = JDBCUtil.update(sql, new Date(),bz, ckjlid);
        return i;
    }

    /**
     * 查询出 所有的 出库审核 未通过的 出库记录表中的 信息
     * @param chuKuCheck
     * @return
     */
    @Override
    public List<Map<String, Object>> getAllNotChuKuCheck(ChuKuCheck chuKuCheck) {
        StringBuffer sql = new StringBuffer("select tc.ckjlid,tc.cdate,tc.uid,tc.totalprice,tc.totalcount,tc.jsr,ts.staffName,tc.status,tc.remark from t_ck_jl tc INNER JOIN t_staff ts ON tc.uid = ts.id where tc.status = 2");
        //ts.staffName LIKE '%小%' and tc.jsr like '%李%'
        if (chuKuCheck.getPerson() != null && chuKuCheck.getPerson() != ""){
            sql.append(" and ts.staffName like '%").append(chuKuCheck.getPerson() + "%'");
        }
        if (chuKuCheck.getJsr() != null && chuKuCheck.getJsr() != ""){
            sql.append(" and tc.jsr like '%").append(chuKuCheck.getJsr()+"%'");
        }
        //cdate >= "2020-02-13"  and cdate <= "2020-08-01"
        if (chuKuCheck.getBeginDate() != null && chuKuCheck.getBeginDate() != ""){
            sql.append(" and cdate >= '"+chuKuCheck.getBeginDate()+"'");
        }
        if (chuKuCheck.getOverDate() !=null && chuKuCheck.getOverDate() != ""){
            sql.append(" and cdate <= '"+chuKuCheck.getOverDate()+"'");
        }
        sql.append(" limit ?,?");
        List<Map<String, Object>> maps = JDBCUtil.queryMap(sql.toString(),(chuKuCheck.getPage()-1)*chuKuCheck.getLimit(),chuKuCheck.getLimit());
        //System.out.println(sql);
        return maps;
    }

    /**
     * 查询 所有的 出库 审核 未通过的 出库 记录表中的信息的 长度
     * @param chuKuCheck
     * @return
     */
    @Override
    public int getAllNotChuKuCheckCount(ChuKuCheck chuKuCheck) {
        StringBuffer sql = new StringBuffer("select count(*) from t_ck_jl tc INNER JOIN t_staff ts ON tc.uid = ts.id  where tc.status = 2");
        if (chuKuCheck.getPerson() != null && chuKuCheck.getPerson() != ""){
            sql.append(" and ts.staffName like '%").append(chuKuCheck.getPerson() + "%'");
        }
        if (chuKuCheck.getJsr() != null && chuKuCheck.getJsr() != ""){
            sql.append(" and tc.jsr like '%").append(chuKuCheck.getJsr()+"%'");
        }
        //cdate >= "2020-02-13"  and cdate <= "2020-08-01"
        if (chuKuCheck.getBeginDate() != null && chuKuCheck.getBeginDate() != ""){
            sql.append(" and cdate >= '"+chuKuCheck.getBeginDate()+"'");
        }
        if (chuKuCheck.getOverDate() !=null && chuKuCheck.getOverDate() != ""){
            sql.append(" and cdate <= '"+chuKuCheck.getOverDate()+"'");
        }
        List<List> lists = JDBCUtil.queryList(sql.toString());
        Object o = lists.get(0).get(0);
        int i = Integer.parseInt(o + "");
        return i;
    }


}
